package com.windfone.dao.impl.jdbc.mysql;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import com.windfone.dao.CommonDao;
import com.windfone.model.Ad;
import com.windfone.model.Color;
import com.windfone.model.DetailMsg;
import com.windfone.model.Goodsqryorder;
import com.windfone.model.OrdersSeq;
import com.windfone.model.PackageCompose;
import com.windfone.model.Pricehistory;
import com.windfone.model.StaticAbout;

public class CommonDaoImpl implements CommonDao {
	private JdbcTemplate jdbcTemplate;

	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	String INSERT_COLOR_SQL = " INSERT INTO color (typeId,productId,colorName )  VALUES ( ?,?,? ) ";
	public int addColor(final Color bean) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
					INSERT_COLOR_SQL,
					new PreparedStatementSetter() {
						public void setValues(PreparedStatement ps)	throws SQLException {
							int i=1;
							ps.setInt(i++,bean.getTypeId());
							ps.setInt(i++,bean.getProductId());
							ps.setString(i++,bean.getColorName());
						}
					}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;	
	}

	String UPDATE_COLOR_SQL = " UPDATE color SET typeId=? ,productId=? ,colorName=?  WHERE id=? ";
	public int updateColor(final Color bean) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
					UPDATE_COLOR_SQL,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						int i=1;
						ps.setInt(i++,bean.getTypeId());
						ps.setInt(i++,bean.getProductId());
						ps.setString(i++,bean.getColorName());
						ps.setInt(i++,bean.getId());
					}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;
	}

	String DELETE_COLOR_SQL = " DELETE FROM  color WHERE id=? ";
	public int deleColor(final int key) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
				DELETE_COLOR_SQL,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						ps.setInt(1, key);
					}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;
	}
 
	String GET_COLOR_BYID_SQL = "  SELECT * FROM  color WHERE   id=? ";
	public Color getColorByKey(final int key) {
		final List<Color> result = new ArrayList<Color>();
		try {
			jdbcTemplate.query(
					GET_COLOR_BYID_SQL,
					new PreparedStatementSetter() {
						public void setValues(PreparedStatement ps)	throws SQLException {
							ps.setInt(1, key);
						}
					},
					new RowCallbackHandler() { 
						public void processRow(ResultSet rs) throws SQLException {
							Color to= new Color();
							to.setId(rs.getInt("id"));
							to.setColorName(rs.getString("colorName"));
							to.setTypeId(rs.getInt("typeId"));
							to.setProductId(rs.getInt("productId"));
							result.add(to);
						}
					}
				);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result.get(0);
	}
	
	String GET_COLOR_BYPRODUCT_SQL = "  SELECT * FROM  color WHERE   productId=? ";
	public List<Color> getColorByProductId(final int productId) {
		final List<Color> result = new ArrayList<Color>();
		try {
			jdbcTemplate.query(
					GET_COLOR_BYPRODUCT_SQL,
					new PreparedStatementSetter() {
						public void setValues(PreparedStatement ps)	throws SQLException {
							ps.setInt(1, productId);
						}
					},
					new RowCallbackHandler() { 
						public void processRow(ResultSet rs) throws SQLException {
							Color to= new Color();
							to.setId(rs.getInt("id"));
							to.setColorName(rs.getString("colorName"));
							to.setTypeId(rs.getInt("typeId"));
							to.setProductId(rs.getInt("productId"));
							result.add(to);
						}
					}
				);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}


	String INSERT_DETAILMSG_SQL = " INSERT INTO detailmsg (typeId,productId,detailMsg )  VALUES ( ?,?,? ) ";
	public int addDetailMsg(final DetailMsg bean) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
					INSERT_DETAILMSG_SQL,
					new PreparedStatementSetter() {
						public void setValues(PreparedStatement ps)	throws SQLException {
							int i=1;
							ps.setInt(i++,bean.getTypeId());
							ps.setInt(i++,bean.getProductId());
							ps.setString(i++,bean.getDetailMsg());
						}
					}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;	
	}
 
	String UPDATE_DETAILMSG_SQL = " UPDATE detailmsg SET typeId=? ,productId=? ,detailMsg=?  WHERE id=? ";
	public int updateDetailMsg(final DetailMsg bean) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
					UPDATE_DETAILMSG_SQL,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						int i=1;
						ps.setInt(i++,bean.getTypeId());
						ps.setInt(i++,bean.getProductId());
						ps.setString(i++,bean.getDetailMsg());
						ps.setInt(i++,bean.getId());
					}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;
	}

	String GET_DETAILMSG_BYID_SQL = "  SELECT * FROM  detailmsg WHERE   id=? ";
	public DetailMsg getDetailMsgByKey(final int key) {
		final List<DetailMsg> result = new ArrayList<DetailMsg>();
		try {
			jdbcTemplate.query(
					GET_COLOR_BYID_SQL,
					new PreparedStatementSetter() {
						public void setValues(PreparedStatement ps)	throws SQLException {
							ps.setInt(1, key);
						}
					},
					new RowCallbackHandler() { 
						public void processRow(ResultSet rs) throws SQLException {
							DetailMsg to= new DetailMsg();
							to.setId(rs.getInt("id"));
							to.setDetailMsg(rs.getString("detailMsg"));
							to.setTypeId(rs.getInt("typeId"));
							to.setProductId(rs.getInt("productId"));
							result.add(to);
						}
					}
				);
		} catch (Exception e) {
			e.printStackTrace();
		}
		if ( result.size()>0)
			return result.get(0);
		else return null;
	}
	String GET_DETAILMSG_BY_PRODUCT_ID_SQL = "  SELECT * FROM  detailmsg WHERE   productId=? ";
	public DetailMsg getDetailMsgByProductId(final int productId) {
		final List<DetailMsg> result = new ArrayList<DetailMsg>();
		try {
			jdbcTemplate.query(
					GET_DETAILMSG_BY_PRODUCT_ID_SQL,
					new PreparedStatementSetter() {
						public void setValues(PreparedStatement ps)	throws SQLException {
							ps.setInt(1, productId);
						}
					},
					new RowCallbackHandler() { 
						public void processRow(ResultSet rs) throws SQLException {
							DetailMsg to= new DetailMsg();
							to.setId(rs.getInt("id"));
							to.setDetailMsg(rs.getString("detailMsg"));
							to.setTypeId(rs.getInt("typeId"));
							to.setProductId(rs.getInt("productId"));
							result.add(to);
						}
					}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		if ( result.size()>0)
			return result.get(0);
		else return null;
	}

	String DELETE_DETAILMSG_SQL = " DELETE FROM  detailmsg WHERE id=? ";
	public int deleteDetailMsg(final int key) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
					DELETE_DETAILMSG_SQL,
					new PreparedStatementSetter() {
						public void setValues(PreparedStatement ps)	throws SQLException {
							ps.setInt(1, key);
						}
					}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;
	}
	
	
	
	String INSERT_PACKAGE_SQL = " INSERT INTO package (typeId,productId,compose,description ,price)  VALUES ( ?,?,?,?,? ) ";
	public int addPackage(final PackageCompose bean) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
					INSERT_PACKAGE_SQL,
					new PreparedStatementSetter() {
						public void setValues(PreparedStatement ps)	throws SQLException {
							int i=1;
							ps.setInt(i++,bean.getTypeId());
							ps.setInt(i++,bean.getProductId());
							ps.setString(i++,bean.getCompose());
							ps.setString(i++,bean.getDescription());
							ps.setDouble(i++,bean.getPrice());
						}
					}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;	
	}
	public int addPackageGetReturnKey(final PackageCompose bean) {
		long rtv = 0;
		KeyHolder keyHolder = new GeneratedKeyHolder();
		try {
			rtv = jdbcTemplate.update(
					new PreparedStatementCreator(){   
						public PreparedStatement createPreparedStatement(Connection conn) throws SQLException {
							PreparedStatement ps =conn.prepareStatement(INSERT_PACKAGE_SQL, new String[]{"id"});//返回id   
							int i=1;
							ps.setInt(i++,bean.getTypeId());
							ps.setInt(i++,bean.getProductId());
							ps.setString(i++,bean.getCompose());
							ps.setString(i++,bean.getDescription());
							ps.setDouble(i++,bean.getPrice());
							return ps;
						}
					},keyHolder);
 
		} catch (Exception e) {
			e.printStackTrace();
		}
		rtv = keyHolder.getKey().longValue();   
		return (int)rtv;	
	}
	
	
	String UPDATE_PACKAGE_SQL = " UPDATE package SET typeId=? ,productId=? ,compose=? , description=?,price=? WHERE id=? ";
	public int updatePackage(final PackageCompose bean) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
					UPDATE_PACKAGE_SQL,
					new PreparedStatementSetter() {
						public void setValues(PreparedStatement ps)	throws SQLException {
							int i=1;
							ps.setInt(i++,bean.getTypeId());
							ps.setInt(i++,bean.getProductId());
							ps.setString(i++,bean.getCompose());
							ps.setString(i++,bean.getDescription());
							ps.setDouble(i++,bean.getPrice());
							ps.setInt(i++,bean.getId());
						}
					}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;
	}
	String DELETE_PACKAGE_SQL = " DELETE FROM  package WHERE id=? ";
	public int delePackage(final int key) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
					DELETE_PACKAGE_SQL,
					new PreparedStatementSetter() {
						public void setValues(PreparedStatement ps)	throws SQLException {
							ps.setInt(1, key);
						}
					}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;
	}
	
	String GET_PACKAGE_BYP_ID_SQL = "  SELECT * FROM  package WHERE   id=? ";
	public PackageCompose getPackageByKey(final int key) {
		final List<PackageCompose> result = new ArrayList<PackageCompose>();
		try {
			jdbcTemplate.query(
					GET_PACKAGE_BYP_ID_SQL,
					new PreparedStatementSetter() {
						public void setValues(PreparedStatement ps)	throws SQLException {
							ps.setInt(1, key);
						}
					},
					new RowCallbackHandler() { 
						public void processRow(ResultSet rs) throws SQLException {
							PackageCompose to= new PackageCompose();
							to.setId(rs.getInt("id"));
							to.setCompose(rs.getString("compose"));
							to.setDescription(rs.getString("description"));
							to.setTypeId(rs.getInt("typeId"));
							to.setProductId(rs.getInt("productId"));
							to.setPrice( rs.getDouble("price"));
							result.add(to);
						}
					}
				);
		} catch (Exception e) {
			e.printStackTrace();
		}
		if ( result.size()>0)
			return result.get(0);
		else 
			return null;
	}
	
	 
	String GET_PACKAGE_BYPRODUCT_SQL = "  SELECT * FROM  package WHERE   productId=? ";
	public List<PackageCompose> getPackageByProductId(final int productId) {
		final List<PackageCompose> result = new ArrayList<PackageCompose>();
		try {
			jdbcTemplate.query(
					GET_PACKAGE_BYPRODUCT_SQL,
					new PreparedStatementSetter() {
						public void setValues(PreparedStatement ps)	throws SQLException {
							ps.setInt(1, productId);
						}
					},
					new RowCallbackHandler() { 
						public void processRow(ResultSet rs) throws SQLException {
							PackageCompose to= new PackageCompose();
							to.setId(rs.getInt("id"));
							to.setCompose(rs.getString("compose"));
							to.setDescription(rs.getString("description"));
							to.setTypeId(rs.getInt("typeId"));
							to.setProductId(rs.getInt("productId"));
							to.setPrice(rs.getDouble("price"));
							result.add(to);
						}
					}
				);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}
	
	String INSERT_STATICABOUT_SQL = " INSERT INTO staticabout (name,content,engligshName,shortName )  VALUES ( ?,?,?,shortName ) ";
	public int addStaticAbout(final StaticAbout bean) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
					INSERT_STATICABOUT_SQL,
					new PreparedStatementSetter() {
						public void setValues(PreparedStatement ps)	throws SQLException {
							int i=1;
							ps.setString(i++,bean.getName());
							ps.setString(i++,bean.getContent());
							ps.setString(i++,bean.getEngligshName());
							ps.setString(i++,bean.getShortName());
						}
					}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;	
	}
	String UPDATE_STATICABOUT_SQL = " UPDATE staticabout SET name=? ,content=? ,engligshName=? ,shortName=? WHERE id=? ";
	public int updateStaticAbout(final StaticAbout bean) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
					UPDATE_STATICABOUT_SQL,
					new PreparedStatementSetter() {
						public void setValues(PreparedStatement ps)	throws SQLException {
							int i=1;
							ps.setString(i++,bean.getName());
							ps.setString(i++,bean.getContent());
							ps.setString(i++,bean.getEngligshName());
							ps.setString(i++,bean.getShortName());
							ps.setInt(i++,bean.getId());
						}
					}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;
	}
	
	String DELETE_STATICABOUT_SQL = " DELETE FROM  staticabout WHERE id=? ";
	public int deleStaticAbout(final int key) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
					DELETE_STATICABOUT_SQL,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						ps.setInt(1, key);
					}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;
	}
	
	
	String GET_STATICABOUT_BY_ID_SQL = "  SELECT * FROM  staticabout WHERE   id=? ";
	public StaticAbout getStaticAboutByKey(final int key) {
		final List<StaticAbout> result = new ArrayList<StaticAbout>();
		try {
			jdbcTemplate.query(
					GET_STATICABOUT_BY_ID_SQL,
					new PreparedStatementSetter() {
						public void setValues(PreparedStatement ps)	throws SQLException {
							ps.setInt(1, key);
						}
					},
					new RowCallbackHandler() { 
						public void processRow(ResultSet rs) throws SQLException {
							StaticAbout to= new StaticAbout();
							to.setId(rs.getInt("id"));
							to.setName(rs.getString("name"));
							to.setContent(rs.getString("content"));
							to.setShortName( rs.getString("shortName"));
							to.setEngligshName( rs.getString("engligshName"));
							result.add(to);
						}
					}
				);
		} catch (Exception e) {
			e.printStackTrace();
		}
		if ( result.size()>0)
			return result.get(0);
		else 
			return null;
	}
	
	String GET_STATICABOUT_BY_SHORTName_SQL = "  SELECT * FROM  staticabout WHERE   shortName=? ";
	public StaticAbout getStaticAboutByShortName(final String shortName) {
		final List<StaticAbout> result = new ArrayList<StaticAbout>();
		try {
			jdbcTemplate.query(
					GET_STATICABOUT_BY_SHORTName_SQL,
					new PreparedStatementSetter() {
						public void setValues(PreparedStatement ps)	throws SQLException {
							ps.setString(1, shortName);
						}
					},
					new RowCallbackHandler() { 
						public void processRow(ResultSet rs) throws SQLException {
							StaticAbout to= new StaticAbout();
							to.setId(rs.getInt("id"));
							to.setName(rs.getString("name"));
							to.setContent(rs.getString("content"));
							to.setShortName( rs.getString("shortName"));
							to.setEngligshName( rs.getString("engligshName"));
							result.add(to);
						}
					}
				);
		} catch (Exception e) {
			e.printStackTrace();
		}
		if ( result.size()>0)
			return result.get(0);
		else 
			return null;
	}
	
	String GET_ALL_STATICABOUT_SQL = "  SELECT * FROM  staticabout ";
	public List<StaticAbout> getAllStaticAbout() {
		final List<StaticAbout> result = new ArrayList<StaticAbout>();
		try {
			jdbcTemplate.query(
					GET_ALL_STATICABOUT_SQL,
					new RowCallbackHandler() { 
						public void processRow(ResultSet rs) throws SQLException {
							StaticAbout to= new StaticAbout();
							to.setId(rs.getInt("id"));
							to.setName(rs.getString("name"));
							to.setContent(rs.getString("content"));
							to.setShortName( rs.getString("shortName"));
							to.setEngligshName( rs.getString("engligshName"));
							result.add(to);
						}
					}
				);
		} catch (Exception e) {
			e.printStackTrace();
		}
			return result;
		 
	}
	public List<StaticAbout> getAllStaticAboutName() {
		final List<StaticAbout> result = new ArrayList<StaticAbout>();
		try {
			jdbcTemplate.query(
					GET_ALL_STATICABOUT_SQL,
					new RowCallbackHandler() { 
						public void processRow(ResultSet rs) throws SQLException {
							StaticAbout to= new StaticAbout();
							to.setId(rs.getInt("id"));
							to.setName(rs.getString("name"));
							to.setShortName( rs.getString("shortName"));
							to.setEngligshName( rs.getString("engligshName"));
							result.add(to);
						}
					}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
		
	}
	String GET_ALL_AD_SQL = "  SELECT * FROM  ad  order by id ";
	public List<Ad> getAllAds() {
		final List<Ad> result = new ArrayList<Ad>();
		try {
			jdbcTemplate.query(
					GET_ALL_AD_SQL,
					new RowCallbackHandler() { 
						public void processRow(ResultSet rs) throws SQLException {
							Ad to= new Ad();
							to.setId(rs.getInt("id"));
							to.setImagepath(rs.getString("imagepath"));
							to.setPosition(rs.getString("position"));
							to.setTarget(rs.getString("target"));
							to.setUrl(rs.getString("url"));
							to.setText(rs.getString("text"));
							result.add(to);
						}
					}
				);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}
	
	String GET_AD_BY_ID_SQL = " SELECT * FROM  ad  WHERE  id=? ";
	public Ad getAdById(final int adId) {
		final List<Ad> result = new ArrayList<Ad>();
		try {
			jdbcTemplate.query(
					GET_AD_BY_ID_SQL,
					new PreparedStatementSetter() {
						public void setValues(PreparedStatement ps)	throws SQLException {
							ps.setInt(1, adId);
						}
					},
					new RowCallbackHandler() { 
						public void processRow(ResultSet rs) throws SQLException {
							Ad to= new Ad();
							to.setId(rs.getInt("id"));
							to.setImagepath(rs.getString("imagepath"));
							to.setPosition(rs.getString("position"));
							to.setTarget(rs.getString("target"));
							to.setUrl(rs.getString("url"));
							to.setText(rs.getString("text"));
							result.add(to);
						}
					}
				);
		} catch (Exception e) {
			e.printStackTrace();
		}
		if ( result.size()>0)
			return result.get(0);
		else 
			return null;
	}

	String UPDATE_AD_SQL = " UPDATE ad SET url=? ,target=? ,imagepath=?,text=?  WHERE id=? ";
	public int updateAd(final Ad bean) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
					UPDATE_AD_SQL,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						int i=1;
						ps.setString(i++,bean.getUrl());
						ps.setString(i++,bean.getTarget());
						ps.setString(i++,bean.getImagepath());
						ps.setString(i++,bean.getText());
						ps.setInt(i++,bean.getId());
					}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;
	}
	
	
	String GET_AD_BYFLAG_SQL = "  SELECT * FROM  ad WHERE flag=? ";
 
	public List<Ad> getAdsByFlag(final String flag) {
		final List<Ad> result = new ArrayList<Ad>();
		try {
			jdbcTemplate.query(
					GET_AD_BYFLAG_SQL,
					new PreparedStatementSetter() {
						public void setValues(PreparedStatement ps)	throws SQLException {
							ps.setString(1, flag);
						}
					},					
					new RowCallbackHandler() { 
						public void processRow(ResultSet rs) throws SQLException {
							Ad to= new Ad();
							to.setId(rs.getInt("id"));
							to.setImagepath(rs.getString("imagepath"));
							to.setPosition(rs.getString("position"));
							to.setTarget(rs.getString("target"));
							to.setUrl(rs.getString("url"));
							to.setText(rs.getString("text"));
							result.add(to);
						}
					}
				);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}
	
	String GET_ORDERS_SEQ_SQL = "  SELECT * FROM  orderseq WHERE keyId=? ";
	public int getDatabaseDaySeq(final int key) {
		final List<OrdersSeq> result = new ArrayList<OrdersSeq>();
		try {
			jdbcTemplate.query(
					GET_ORDERS_SEQ_SQL,
					new PreparedStatementSetter() {
						public void setValues(PreparedStatement ps)	throws SQLException {
							ps.setInt(1, key);
						}
					},					
					new RowCallbackHandler() { 
						public void processRow(ResultSet rs) throws SQLException {
							OrdersSeq to= new OrdersSeq();
							to.setKey(rs.getInt("keyId"));
							to.setDaySeq(rs.getInt("daySeq"));
							result.add(to);
						}
					}
				);
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		if (result.size()>0)
			return result.get(0).getDaySeq();
		else {
			addDatabaseDaySeq(key);
			return 0;
		}
	}
	private int addDatabaseDaySeq(final int key){
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
					"INSERT INTO orderseq (keyId,daySeq) VALUES (?,?)" ,
					new PreparedStatementSetter() {
						public void setValues(PreparedStatement ps)	throws SQLException {
							int i=1;
							ps.setInt(i++,key);
							ps.setInt(i++,1);
						}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;
	}
	
	String UPDATE_ORDERS_SEQ_SQL = " UPDATE orderseq SET daySeq=?  WHERE keyId=?";
	public int setDatabaseDaySeq(final int daySeq, final int key) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
					UPDATE_ORDERS_SEQ_SQL,
					new PreparedStatementSetter() {
						public void setValues(PreparedStatement ps)	throws SQLException {
							int i=1;
							ps.setInt(i++,daySeq);
							ps.setInt(i++,key);
						}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;
	}
	
	String INSERT_PRICE_HISTORY_SQL = " INSERT INTO pricehistory (productId,description,price,standar," +
			"ranges,keygroup,oldprice,packageId )  VALUES ( ?,?,?,?, ?,?,?,? ) ";
 
	public int addPricehistory(final Pricehistory bean) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
					INSERT_PRICE_HISTORY_SQL,
					new PreparedStatementSetter() {
						public void setValues(PreparedStatement ps)	throws SQLException {
							int i=1;
							ps.setInt(i++,bean.getProductId() );
							ps.setString(i++,bean.getDescription() );
							ps.setDouble(i++,bean.getPrice());
							ps.setString(i++,bean.getStandar() );
							ps.setDouble(i++,bean.getRange());
							ps.setInt(i++,bean.getKeygroup() );
							ps.setDouble(i++,bean.getOldprice());
							ps.setInt(i++,bean.getPackageId() );
						}
					}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;	
	}
	
//	productId,description,price,standar,range,keygroup,oldprice,packageId
	String UPDATE_PRICE_HISTORY_SQL = " UPDATE pricehistory SET price=?," +
			"ranges=?  WHERE id=?"; //description=?,,oldprice=?,standar=?,
	public int updatePricehistory(final Pricehistory bean) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
					UPDATE_PRICE_HISTORY_SQL,
					new PreparedStatementSetter() {
						public void setValues(PreparedStatement ps)	throws SQLException {
							int i=1;
//							ps.setString(i++, bean.getDescription());
							ps.setDouble(i++,bean.getPrice());
//							ps.setString(i++,bean.getStandar());
							ps.setDouble(i++,bean.getRange());
//							ps.setDouble(i++,bean.getOldprice());
							ps.setInt(i++,bean.getId());
						}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;
	}
	
	String DELETE_PRICE_HISTORY_SQL = " DELETE FROM  pricehistory WHERE id=? ";
	public int deletePricehistory(final Pricehistory bean) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
					DELETE_PRICE_HISTORY_SQL,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						ps.setInt(1, bean.getId());
					}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;
	}
 
	public List<Pricehistory> listOneGroupPricehistory(int begin, int end) {
		return null;
	}
	
	String LIST_PRICE_HISTORY_BYGROUP_SQL = "  SELECT p.*,t.name as productName FROM  pricehistory p,product t WHERE p.productId=t.id AND keygroup IN ";
	public List<Pricehistory> listOneGroupPricehistory(List<Integer> keyGroups) {
		StringBuffer buf = new StringBuffer();
		for(int i=0;i<keyGroups.size();i++){
			if ( i==0)
				buf.append( keyGroups.get(i));
			else
				buf.append(",").append( keyGroups.get(i));
				
		}
		String sql = LIST_PRICE_HISTORY_BYGROUP_SQL + "( " + buf.toString() + " )  ORDER BY  keygroup";
		final List<Pricehistory> result = new ArrayList<Pricehistory>();
		if ( keyGroups.size() >0 )
		try {
			jdbcTemplate.query(
					sql,
					new RowCallbackHandler() { 
						public void processRow(ResultSet rs) throws SQLException {
							Pricehistory to= new Pricehistory();
							to.setId(rs.getInt("id"));
							to.setProductName(rs.getString("productName"));
							to.setDescription(rs.getString("description"));
							to.setPrice(rs.getDouble("price"));
							to.setStandar( rs.getString("standar"));
							to.setRange(rs.getDouble("ranges"));
							to.setCreateTime(rs.getTimestamp("createTime"));
							to.setOldprice(rs.getDouble("oldprice"));
							to.setProductId(rs.getInt("productId"));
							to.setPackageId(rs.getInt("packageId"));
							to.setKeygroup(rs.getInt("keygroup"));
							result.add(to);
						}
					}
				);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}
	
	String CHECK_PRICE_HISTORY_SQL = "  SELECT * FROM  pricehistory WHERE   packageId=? AND keygroup=? AND productId=? ";
	public Pricehistory checkPriceExist(final Pricehistory bean) {
		final List<Pricehistory> result = new ArrayList<Pricehistory>();
		try {
			jdbcTemplate.query(
					CHECK_PRICE_HISTORY_SQL,
					new PreparedStatementSetter() {
						public void setValues(PreparedStatement ps)	throws SQLException {
							ps.setInt(1, bean.getPackageId());
							ps.setInt(2, bean.getKeygroup());
							ps.setInt(3, bean.getProductId());
						}
					},
					new RowCallbackHandler() { 
						public void processRow(ResultSet rs) throws SQLException {
							Pricehistory to= new Pricehistory();
							to.setId(rs.getInt("id"));
							to.setDescription(rs.getString("description"));
							to.setPrice(rs.getDouble("price"));
							to.setStandar( rs.getString("standar"));
							to.setRange(rs.getDouble("ranges"));
							to.setCreateTime(rs.getTimestamp("createTime"));
							to.setOldprice(rs.getDouble("oldprice"));
							to.setProductId(rs.getInt("productId"));
							to.setPackageId(rs.getInt("packageId"));
							to.setKeygroup(rs.getInt("keygroup"));
							result.add(to);
						}
					}
				);
		} catch (Exception e) {
			e.printStackTrace();
		}
		if ( result.size()>0)
			return result.get(0);
		else
			return null;
	}
	
	
	String QRY_PRICE_HISTORY_SQL = "  SELECT h.*,p.name as productName FROM pricehistory h,product p,producttype t WHERE h.productid=p.id AND " +
			"p.producttypeid=t.id   ";
	public List<Pricehistory> qryPriceHistory(String wherecondition) {
		final List<Pricehistory> result = new ArrayList<Pricehistory>();
		String sql = QRY_PRICE_HISTORY_SQL + wherecondition;
		try {
			jdbcTemplate.query(
					sql ,
					new RowCallbackHandler() { 
						public void processRow(ResultSet rs) throws SQLException {
							Pricehistory to= new Pricehistory();
							to.setId(rs.getInt("id"));
							to.setDescription(rs.getString("description"));
							to.setPrice(rs.getDouble("price"));
							to.setStandar( rs.getString("standar"));
							to.setRange(rs.getDouble("ranges"));
							to.setCreateTime(rs.getTimestamp("createTime"));
							to.setOldprice(rs.getDouble("oldprice"));
							to.setProductId(rs.getInt("productId"));
							to.setPackageId(rs.getInt("packageId"));
							to.setProductName(rs.getString("productName"));
							to.setKeygroup(rs.getInt("keygroup"));
							result.add(to);
						}
					}
				);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}
	public List<Pricehistory> getAllKewgroups( ) {
		final List<Pricehistory> result = new ArrayList<Pricehistory>();
		String sql = " SELECT keygroup FROM pricehistory GROUP by keygroup";
		try {
			jdbcTemplate.query(
					sql ,
					new RowCallbackHandler() { 
						public void processRow(ResultSet rs) throws SQLException {
							Pricehistory to= new Pricehistory();
							to.setKeygroup(rs.getInt("keygroup"));
							result.add(to);
						}
					}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}
	
	public List listOnePagePricehistory(int[] keygroups) {
		StringBuffer buf = new StringBuffer();
		for(int i=0;i<keygroups.length;i++){
			if ( i==0)
				buf.append( keygroups[i]);
			else
				buf.append(",").append( keygroups[i]);
				
		}
		String sql = LIST_PRICE_HISTORY_BYGROUP_SQL + "( " + buf.toString() + " )  ORDER BY  keygroup";
		final List<Pricehistory> result = new ArrayList<Pricehistory>();
		try {
			jdbcTemplate.query(
					sql,
					new RowCallbackHandler() { 
						public void processRow(ResultSet rs) throws SQLException {
							Pricehistory to= new Pricehistory();
							
							to.setId(rs.getInt("id"));
							to.setProductName(rs.getString("productName"));
							to.setDescription(rs.getString("description"));
							to.setPrice(rs.getDouble("price"));
							to.setStandar( rs.getString("standar"));
							to.setRange(rs.getDouble("ranges"));
							to.setCreateTime(rs.getTimestamp("createTime"));
							to.setOldprice(rs.getDouble("oldprice"));
							to.setProductId(rs.getInt("productId"));
							to.setPackageId(rs.getInt("packageId"));
							to.setKeygroup(rs.getInt("keygroup"));
					 
							result.add(to);
						}
					}
				);
		} catch (Exception e) {
			e.printStackTrace();
		}
		List rtvLs = new ArrayList();
		
		for(int i=0;i<result.size();i++){
			List l = new ArrayList();
			boolean findOne = false;
			Pricehistory p = result.get(i);
			if ( p.isFlag() == false){
				p.setFlag(true);
				findOne = true;
				l.add( p );
			}
			
			for(int j=i+1;j<result.size();j++){
				Pricehistory p2 = result.get(j);
				if ( p.getKeygroup() == p2.getKeygroup() ){
					if ( p2.isFlag() == false){
						p2.setFlag(true);
						l.add(p2);
					}
				}
			}
			
			if ( findOne )
				rtvLs.add(l);
		}
		return rtvLs;
	}
	
	String   DELETE_PRICE_SQL = " DELETE FROM pricehistory WHERE id=?";
	public int delPriceByKey(final int priceHisId) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
				DELETE_PRICE_SQL,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						ps.setInt(1, priceHisId);
					}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;
	}
	
	

	String INSERT_GOODS_QRY_SQL = " INSERT INTO goodqryorder (name,sendOrderSeq,receiveAddress,orderseq,keygroup )  VALUES ( ?,?,?,? ) ";
	public int inserGoodsqryorder(final Goodsqryorder bean) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
					INSERT_GOODS_QRY_SQL,
					new PreparedStatementSetter() {
						public void setValues(PreparedStatement ps)	throws SQLException {
							int i=1;
							ps.setString(i++,bean.getName());
							ps.setString(i++,bean.getSendOrderSeq());
							ps.setString(i++,bean.getReceiveAddress());
							ps.setString(i++,bean.getOrderseq());
							ps.setInt(i++,bean.getKeygroup());
						}
					}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;	
	}
	
	
//	  Id int(11) NOT NULL AUTO_INCREMENT,
//	  name varchar(30) DEFAULT NULL,
//	  sendOrderSeq varchar(50) DEFAULT NULL,
//	  receiveAddress varchar(255) DEFAULT NULL,
//	  orderseq varchar(50) DEFAULT NULL,
//	  keygroup int(11) DEFAULT NULL,
//	  createTime timestamp NULL DEFAULT CURRENT_TIMESTAMP,
	String UPDATE_GOODS_QRY_SQL = " UPDATE goodqryorder SET name=? ,sendOrderSeq=?,orderseq=? ,receiveAddress=?,keygroup=?  WHERE id=? ";
 
	public int updateGoodsqryorder(final Goodsqryorder bean) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
					UPDATE_GOODS_QRY_SQL,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						int i=1;
						ps.setString(i++,bean.getName());
						ps.setString(i++,bean.getSendOrderSeq());
						ps.setString(i++,bean.getOrderseq());
						ps.setString(i++,bean.getReceiveAddress());
						ps.setInt(i++,bean.getKeygroup());
						ps.setInt(i++,bean.getId());
					}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;
	}
	
	String   DELETE_GOODS_QRY_ORDER_SQL = " DELETE FROM goodqryorder WHERE id=?";
	public int deleGoodsqryorder(final Goodsqryorder bean) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(
					DELETE_GOODS_QRY_ORDER_SQL,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						ps.setInt(1, bean.getId());
					}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;
	}
	
	public List listOnePageGoodsqryorder(int[] keygroups) {
		return null;
	}
}
